<?php
function __rep_total_poin_karyawan($karyID, $startMonth, $endMonth, $year){
	$data = array();
	for($month=$startMonth; $month<=$endMonth; $month++){
		$result = rank_select(false, "a.KODE_KARYAWAN='$karyID' AND year(RANK_PERIODE)='$year' AND month(RANK_PERIODE)='$month'");
		$tmp = mysql_fetch_assoc($result);
		$data["$month-$year"] = $tmp['RANK_POINT']? $tmp['RANK_POINT'] : 0;	
	}
	return $data;
}

function __rep_rata_departemen($departemenID, $startMonth, $endMonth, $year){
	$data = array();
	for($month=$startMonth; $month<=$endMonth; $month++){
		$result = mysql_query(
			"SELECT a.PERB_NO, a.KODE_KARYAWAN, a1.DEPERB_NO, a1.DETACHIEV_ID, a1.DEPERB_COUNT, 
					a1.DEPERB_STATUS,
					(SELECT NILAI_POINT 
					 FROM nilai as xx 
					 WHERE xx.DETACHIEV_ID = a1.DETACHIEV_ID AND 
					 		a1.DEPERB_COUNT BETWEEN xx.NILAI_MIN AND xx.NILAI_MAX
					) AS POINT
			FROM perbuatan as a, detil_perbuatan as a1, 
				data_karyawan as b, relasi_div_jab_din as c, 
				dep_divisi_jabatan as d, data_department as e
			WHERE a.PERB_NO = a1.PERB_NO AND
				a1.DEPERB_STATUS='2' AND 
				a.KODE_KARYAWAN = b.KODE_KARYAWAN AND
				b.KODE_KARYAWAN  = c.KODE_KARYAWAN AND
				c.ID_DEP_DIV_JAB = d.ID_DEP_DIV_JAB AND
				d.ID_DEPARTMENT  = e.ID_DEPARTMENT AND
				e.ID_DEPARTMENT  = '$departemenID' AND
				year(a.PERB_TGL)='$year' AND month(a.PERB_TGL)='$month'");
		
		//1. sum the result
		$data["$month-$year"] = 0;
		$dv = 0;
		while ($tmp = mysql_fetch_assoc($result)){
			$data["$month-$year"] += $tmp['POINT']? $tmp['POINT'] : 0;
			$dv++;
		}
		
		//2. devide it
		if ($dv) $data["$month-$year"] = $data["$month-$year"]/$dv;
	}
	return $data;
}

function __rep_rata_departemen2($departemenID, $startPeriode, $endPeriode){
	$data = array();
	foreach ($departemenID as $DEPT){
		$result = "SELECT a.PERB_NO, a.KODE_KARYAWAN, a1.DEPERB_NO, a1.DETACHIEV_ID, a1.DEPERB_COUNT, 
					a1.DEPERB_STATUS,
					(SELECT NILAI_POINT 
					 FROM nilai as xx 
					 WHERE xx.DETACHIEV_ID = a1.DETACHIEV_ID AND 
							a1.DEPERB_COUNT BETWEEN xx.NILAI_MIN AND xx.NILAI_MAX
					) AS POINT,
					e.ID_DEPARTMENT, e.NAMA_DEPARTMENT
			FROM perbuatan as a, detil_perbuatan as a1, 
				data_karyawan as b, relasi_div_jab_din as c, 
				dep_divisi_jabatan as d, data_department as e
			WHERE a.PERB_NO = a1.PERB_NO AND
				a1.DEPERB_STATUS='2' AND 
				a.KODE_KARYAWAN = b.KODE_KARYAWAN AND
				b.KODE_KARYAWAN  = c.KODE_KARYAWAN AND
				c.ID_DEP_DIV_JAB = d.ID_DEP_DIV_JAB AND
				d.ID_DEPARTMENT  = e.ID_DEPARTMENT AND
				e.ID_DEPARTMENT = '$DEPT' AND
				DATE_FORMAT(a.PERB_TGL, '%Y-%m') BETWEEN DATE_FORMAT('$startPeriode', '%Y-%m') AND 
												DATE_FORMAT('$endPeriode', '%Y-%m')";
		
		//load nama departemen, untuk key
		$key = mysql_fetch_assoc(departemen_load($DEPT));
		$key = $key['NAMA_DEPARTMENT'];
		
		//1. sum the result
		$dv = 0;
		$result = mysql_query($result);
		while ($tmp = mysql_fetch_assoc($result)){
			$data[$key] += $tmp['POINT']? $tmp['POINT'] : 0;
			$dv++;
		}
		
		if (!$data[$key]) $data[$key] = 0;
		
		//2. devide it
		if ($dv) $data[$key] = $data[$key]/$dv;
	}
	return $data;
}

function __rep_rata_seluruh_poin($startMonth, $endMonth, $year){
	$data = array();
	for($month=$startMonth; $month<=$endMonth; $month++){
		$result = mysql_query(
			"SELECT a.PERB_TGL, a.KODE_KARYAWAN, b.NAMA_KARYAWAN, e.NAMA_DEPARTMENT, a1.DETACHIEV_ID, 
					f.ACHIEV_JENIS, f.ACHIEV_BIDANG, g.DETACHIEV_TINGKAT, g.DETACHIEV_NAMA,
					a1.DEPERB_COUNT, 
						(SELECT NILAI_POINT 
						 FROM nilai as xx 
						 WHERE xx.DETACHIEV_ID = a1.DETACHIEV_ID AND 
								a1.DEPERB_COUNT BETWEEN xx.NILAI_MIN AND xx.NILAI_MAX
						) AS POINT
				FROM perbuatan as a, detil_perbuatan as a1, 
					data_karyawan as b, relasi_div_jab_din as c, 
					dep_divisi_jabatan as d, data_department as e,
					prestasipelanggaran as f, detil_prestasi_pelanggaran as g
				WHERE a.PERB_NO = a1.PERB_NO AND
					a1.DEPERB_STATUS='2' AND 
					a.KODE_KARYAWAN = b.KODE_KARYAWAN AND
					b.KODE_KARYAWAN  = c.KODE_KARYAWAN AND
					c.ID_DEP_DIV_JAB = d.ID_DEP_DIV_JAB AND
					d.ID_DEPARTMENT  = e.ID_DEPARTMENT AND
					f.ACHIEV_ID = g.ACHIEV_ID AND 
					g.DETACHIEV_ID = a1.DETACHIEV_ID AND
					year(a.PERB_TGL)='$year' AND month(a.PERB_TGL)='$month'
			ORDER BY NAMA_DEPARTMENT, NAMA_KARYAWAN, ACHIEV_BIDANG, DEPERB_COUNT");
		
		//1. sum it
		$dv = 0;
		while ($tmp = mysql_fetch_assoc($result)){
			$data["$month-$year"] += $tmp['POINT']? $tmp['POINT'] : 0;	
			$dv++;
		}
		
		//no data, normalize it...
		if (!$data["$month-$year"]) $data["$month-$year"] = 0;
		
		//2. devide it
		if ($dv) $data["$month-$year"] = $data["$month-$year"]/$dv;
	}
	return $data;
}

function __rep_rata_seluruh_poin2($startPeriode, $endPeriode){
	$result = mysql_query(
		"SELECT a.PERB_TGL, a.KODE_KARYAWAN, b.NAMA_KARYAWAN, e.NAMA_DEPARTMENT, a1.DETACHIEV_ID, 
				f.ACHIEV_JENIS, f.ACHIEV_BIDANG, g.DETACHIEV_TINGKAT, g.DETACHIEV_NAMA,
				a1.DEPERB_COUNT, 
					(SELECT NILAI_POINT 
					 FROM nilai as xx 
					 WHERE xx.DETACHIEV_ID = a1.DETACHIEV_ID AND 
							a1.DEPERB_COUNT BETWEEN xx.NILAI_MIN AND xx.NILAI_MAX
					) AS POINT
			FROM perbuatan as a, detil_perbuatan as a1, 
				data_karyawan as b, relasi_div_jab_din as c, 
				dep_divisi_jabatan as d, data_department as e,
				prestasipelanggaran as f, detil_prestasi_pelanggaran as g
			WHERE a.PERB_NO = a1.PERB_NO AND
				a1.DEPERB_STATUS='2' AND 
				a.KODE_KARYAWAN = b.KODE_KARYAWAN AND
				b.KODE_KARYAWAN  = c.KODE_KARYAWAN AND
				c.ID_DEP_DIV_JAB = d.ID_DEP_DIV_JAB AND
				d.ID_DEPARTMENT  = e.ID_DEPARTMENT AND
				f.ACHIEV_ID = g.ACHIEV_ID AND 
				g.DETACHIEV_ID = a1.DETACHIEV_ID AND
				DATE_FORMAT(a.PERB_TGL, '%Y-%m') BETWEEN DATE_FORMAT('$startPeriode', '%Y-%m') AND 
												DATE_FORMAT('$endPeriode', '%Y-%m')
		ORDER BY NAMA_DEPARTMENT, NAMA_KARYAWAN, ACHIEV_BIDANG, DEPERB_COUNT");
	
	//1. sum it
	$data = 0;
	$dv = 0;
	while ($tmp = mysql_fetch_assoc($result)){
		$data+= $tmp['POINT']? $tmp['POINT'] : 0;	
		$dv++;
	}
	
	//no data, normalize it...
	if (!$data) $data = 0;
	
	//2. devide it
	if ($dv) $data= $data/$dv;
	
	return $data;
}

//graph function
function __createLinePlot($data, $line, $mark=false, $value=false, $legend=false, $csim=false){
	$lineplot=new LinePlot($data);
	$lineplot->SetColor($line['color']);
	$lineplot->SetWeight($line['weight']);
	if ($mark){
		if ($mark['type']) 		$lineplot->mark->SetType($mark['type']);
		if ($mark['fillColor']) $lineplot->mark->SetFillColor($mark['fillColor']);
		if ($mark['width'])		$lineplot->mark->SetWidth($mark['width']);
	}
	if ($value){
		if ($value['color'])	$lineplot->value ->SetColor($value['color']);
		if ($value['font'][0] && $value['font'][1])		$lineplot ->value->SetFont($value['font'][0], $value['font'][1]); 
		if ($value['show']) 	$lineplot ->value->Show();	
	}
	
	if ($legend){
		if ($legend['title'])	$lineplot->SetLegend($legend['title']);
	}
	
	if ($csim) {
		if ($csim['target'] && $csim['alt'])	$lineplot->SetCSIMTargets($csim['target'],$csim['alt']);
	}
	return $lineplot;
}